"""
# 打开的是xlsx,返回时写入新的xlsx,可以借鉴 精简.py
# 先获得是单选或多选的问题
# 字符串切割做减法
# 依次匹配A B C D到末位,匹配的干净利落(删空格,换行,<br>,等等),方便将这个用于js的遍历
# 即遍历返回的'干净项'是否包含于冗杂的网页选项中
# 返回的列表用于填充字典的值
# 顺便把问题也切出来,建议保留这个全文,js捕捉到的问题建议使用前几个字,取决于双边的空格处理决策
# python处理的问题的空格删除,选项做了全缩掉空格以及删除<br>
# 任何空格都不要有，完美字典{问题:正确答案} ，这个答案是原汁原味的选项
# js尝试匹配值时，是需要匹配去除“中文符号，英文符号”，之外的问题本身即可
"""
# coding=UTF-8
import re
import xlrd
import openpyxl
from os import listdir
from os.path import splitext


def write_new_xlsx(path, value: dict, sheet_name='send_to_song'):
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = sheet_name
    i = 0
    for k, v in value.items():
        i += 1
        sheet.cell(row=i, column=1, value=k)
        text = ''
        for v_v in v:
            text += v_v
        sheet.cell(row=i, column=2, value=text)
    workbook.save(path.replace('.xlsx', '.完美选项.xlsx'))
    print("{}表格写入数据成功！".format(path))


def cutting_options(file_path):
    """
    切割出四个选项以及问题,返回完美字典  {问题:[A,B,C,D,right]}
    :param file_path: 完整的xlsx文件路径
    :return: {问题:[A,B,C,D,right]}
    """
    wb = xlrd.open_workbook(file_path)
    sh = wb.sheet_by_name('send_to_song')
    # print('元数据行数 ', sh.nrows, end='\n')  # 有效数据行数
    list_only_question = []  # 中间件列表,用来过滤掉非选择题
    list_only_answer = []  # 中间件列表,用来过滤掉非选择题
    for i in range(sh.nrows):
        # 判断是否四个选项都在这个题里,在就加入中间件列表,奇数项表示他的答案
        if 'A.' in sh.cell(i, 0).value \
                and 'B.' in sh.cell(i, 0).value \
                and 'C.' in sh.cell(i, 0).value \
                and 'D.' in sh.cell(i, 0).value:
            list_only_question.append(sh.cell(i, 0).value)
            list_only_answer.append(sh.cell(i, 1).value)
    # print(len(list_only_question), len(list_only_answer))
    # print(list_complete_q_and_answer)
    """匹配问题以及选项，永远不要忘记转义！还有元字符的使用"""
    zh_symbol = re.compile(r"[\u3002|\uff1f|\uff01|\uff0c|\u3001|\uff1b|\uff1a|\u201c|\u201d"
                           r"|\u2018|\u2019|\uff08|\uff09|\u300a|\u300b|\u3008|\u3009|\u3010"
                           r"|\u3011|\u300e|\u300f|\u300c|\u300d|\ufe43|\ufe44|\u3014|\u3015"
                           r"|\u2026|\u2014|\uff5e|\ufe4f|\uffe5]", re.S)
    en_symbol = re.compile(r"[\.!@#\$%\\\^&\*\)\(\+=\{\}\[\]\/\",\'<>~\·`\?:;|]", re.S)
    q_obj = re.compile(r'^(?P<Q>.*?)A\.', re.S)
    a_obj = re.compile(R'.*?A\.(?P<A>.*?)B\.', re.S)
    b_obj = re.compile(R'.*?B\.(?P<B>.*?)C\.', re.S)
    c_obj = re.compile(R'.*?C\.(?P<C>.*?)D\.', re.S)
    d_obj = re.compile(R'.*?D\.(?P<D>.*?)$', re.S)
    """
    遍历分解出，问题，选项*4，答案来自list_complete_q_and_answer的奇数位
    创建一个新字典，用来做完美字典{问题:正确答案}
    """
    perfect_dictionary = {}
    li_count = 0  # 该计数器步进为1单独用于答案列表
    for li in list_only_question:
        ret_q = q_obj.finditer(li)
        q_mid = ''
        for i in ret_q:
            ret_zh = zh_symbol.finditer(i.group())
            ret_en = en_symbol.finditer(i.group())
            q_mid = i.group('Q').replace('<br>', '').replace(' ', '')
            for symbol in ret_zh:
                # 去除中文标点符号
                q_mid = q_mid.replace(symbol.group(), '')
            for symbol in ret_en:
                # 去除英文标点符号
                q_mid = q_mid.replace(symbol.group(), '')
            # 问题继续做字典的键，值设置成一个空列表
        perfect_dictionary[q_mid] = []
        ret_a = a_obj.finditer(li)
        option_a = ''
        for A in ret_a:
            option_a = A.group('A').replace('<br>', '').replace(' ', '')
            # print(option_a)
        # print(li_count)
        if 'A' in list_only_answer[li_count]:
            perfect_dictionary[q_mid].append(option_a)
        ret_b = b_obj.finditer(li)
        option_b = ''
        for B in ret_b:
            option_b = B.group('B').replace('<br>', '').replace(' ', '')
            # print(option_b)
        if 'B' in list_only_answer[li_count]:
            perfect_dictionary[q_mid].append(option_b)
        ret_c = c_obj.finditer(li)
        option_c = ''
        for C in ret_c:
            option_c = C.group('C').replace('<br>', '').replace(' ', '')
            # print(option_c)
        if 'C' in list_only_answer[li_count]:
            perfect_dictionary[q_mid].append(option_c)
        ret_d = d_obj.finditer(li)
        option_d = ''
        for D in ret_d:
            option_d = D.group('D').replace('<br>', '').replace(' ', '')
            # print(option_d)
        if 'D' in list_only_answer[li_count]:
            perfect_dictionary[q_mid].append(option_d)
        li_count += 1
    # print(perfect_dictionary)
    # print(sh.cell(0, 0).value)  # 输出第一行第一列的值
    # print(sh.row_values(0))  # 输出第一行的所有值
    # 将数据和标题组合成字典
    # 创建新字典,键值不为空,就不写入
    # final_dict = {}
    # for i in range(sh.nrows):
    #     if sh.cell(i, 1).value != '' and sh.cell(i, 1).value != ' ':
    #         final_dict[sh.cell(i, 0).value] = sh.cell(i, 1).value
    # print('精简后行数 ', len(final_dict), end='')  # 有效数据行数
    # print('精简比', sh.nrows / len(final_dict))
    return perfect_dictionary


def get_all_xlsx(path_dir_='D:\\send_to_song\\'):
    """
    接受文件夹路径，返回所有xlsx文件路径的列表
    :param path_dir_:
    :return:
    """
    filenames_lst = listdir(path_dir_)
    xlsx_lst_ = []
    for item in filenames_lst:
        if splitext(item)[1] == '.xlsx':
            xlsx_lst_.append('D:\\send_to_song\\{}'.format(str(item)))
    return xlsx_lst_


if __name__ == '__main__':
    # filepath = 'D:\\send_to_song\\虚拟化_高云强_大题有答案没精简.精简副本.xlsx'
    # pf_dict = cutting_options(filepath)  # 返回完美字典
    all_xlsx_path = get_all_xlsx("D:\\send_to_song\\")
    for xlsx_path in all_xlsx_path:
        perfect_dict = cutting_options(xlsx_path)
        write_new_xlsx(xlsx_path, perfect_dict)
